package DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import Helper.Util;
import Model.Cargo;

public class CargoDAO implements ItfBanco<Cargo> {

	@Override
	public void inserir(Cargo cargo) throws SQLException {
		
		Connection con = Conexao.getConexao();
		String sql = "INSERT INTO CARGO VALUES(?,?)";
		
		try(PreparedStatement pstm = con.prepareStatement(sql)) {
			
			pstm.setString(1, cargo.getNome());
			pstm.setInt(2, cargo.getStatus());
			
			pstm.execute();
			
		} catch (Exception e) {
			System.out.println("Erro ao inserir cargo: ");
			e.printStackTrace();
		}
		
		con.close();
	}

	@Override
	public void atualizar(Cargo cargo) throws SQLException {
		Connection con = Conexao.getConexao();
		String sql = "UPDATE CARGO SET"
				+ "			NOME_CARGO = ?,"
				+ "			STATUS_CARGO = ?"
				+ "   WHERE ID_CARGO = ?";
		
		try(PreparedStatement pstm = con.prepareStatement(sql)) {
			
			pstm.setString(1, cargo.getNome());
			pstm.setInt(2, cargo.getStatus());
			pstm.setInt(3, cargo.getId());
			
			pstm.executeUpdate();
			
		} catch (Exception e) {
			System.out.println("Erro ao atualizar cargo: ");
			e.printStackTrace();
		}
		
		con.close();
	}

	@Override
	public Cargo listar(Cargo cargo) throws SQLException {
		Connection con = Conexao.getConexao();
		
		String sql = "SELECT ID_CARGO,"
				+ "          NOME_CARGO, "
				+ "			 STATUS_CARGO"
				+ "   FROM CARGO"
				+ "   WHERE ID_CARGO = ?";
		
		ResultSet rs;
		
		try(PreparedStatement pstm = con.prepareStatement(sql)) {
			
			pstm.setInt(1, cargo.getId());
			
			rs = pstm.executeQuery();
			
			while(rs.next()){
				cargo.setId(rs.getInt("ID_CARGO"));
				cargo.setNome(rs.getString("NOME_CARGO"));
				cargo.setStatus(rs.getInt("STATUS_CARGO"));
			}
			
		} catch (Exception e) {
			System.out.println("Erro ao listar cargo: ");
			e.printStackTrace();
		}	
		
		con.close();
		return cargo;
	}

	@Override
	public ArrayList<Cargo> listarTodos() throws SQLException {
		
		ArrayList<Cargo> lista = new ArrayList<>();
		
		Connection con = Conexao.getConexao();
		String sql = "SELECT ID_CARGO,"
				+ "          NOME_CARGO, "
				+ "			 STATUS_CARGO"
				+ "   FROM CARGO"
				+ "	  WHERE STATUS_CARGO <> ? "
				+ "   ORDER BY NOME_CARGO ASC";
		
		ResultSet rs;
		PreparedStatement pstm = con.prepareStatement(sql);
		pstm.setInt(1, Util.stExcluido);
		
		rs = pstm.executeQuery();
		
		while (rs.next()){
			Cargo cargo = new Cargo();
			cargo.setId(rs.getInt("ID_CARGO"));
			cargo.setNome(rs.getString("NOME_CARGO"));
			cargo.setStatus(rs.getInt("STATUS_CARGO"));
			lista.add(cargo);
		}
		
		con.close();
		return lista;
	}
	
}
